package com.wenjie.controller.sys;

import com.mybatisflex.core.query.QueryOrderBy;
import com.wenjie.databseDiff.entity.DatasourceConnectionTestReq;
import com.wenjie.databseDiff.entity.DbDiffReq;
import com.wenjie.databseDiff.entity.ResultData;
import com.wenjie.databseDiff.enums.DatasourceType;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.DatasourceSynchronizer;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp.H2DatasourceSynchronizer;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp.MysqlDatasourceSynchronizer;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp.OracleDatasourceSynchronizer;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp.PgsqlDatasourceSynchronizer;
import com.wenjie.databseDiff.handle.service.DbDifService;
import com.wenjie.original.entity.SysDiffDatasource;
import com.wenjie.original.entity.common.vo.RestBaseVo;
import com.wenjie.original.entity.table.SysDiffDatasourceTableDef;
import com.wenjie.original.entity.vo.SysDiffDatasource.SysDiffDatasourceUpOrAddVo;
import com.wenjie.original.entity.vo.SysDiffDatasource.SysDiffDatasourceVo;
import com.wenjie.original.service.ISysDiffDatasourceService;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * @author wenjie
 * @Description 数据库比对工具
 * @date 2024/8/29 15:34
 */
@RestController
@RequestMapping("/api")
public class DbDiffController {

    @Autowired
    private DbDifService dbDifService;

    @Autowired
    private ISysDiffDatasourceService sysDiffDatasourceService;

    @Autowired
    private ISysDiffDatasourceService datasourceService;

    @ApiOperation("添加")
    @PostMapping("datasourceManage/add")
    public void add(@RequestBody SysDiffDatasourceUpOrAddVo.AddVo req) {
        sysDiffDatasourceService.add(req);
    }

    @ApiOperation("查询")
    @PostMapping("datasourceManage/list")
    public RestBaseVo<SysDiffDatasourceVo.ResVo> list(@RequestBody SysDiffDatasourceVo.SelVo selVo) {
        QueryOrderBy[] asc = {SysDiffDatasourceTableDef.SYS_DIFF_DATASOURCE.CREATE_TIME.asc()};
        return sysDiffDatasourceService.list(selVo, asc, SysDiffDatasourceVo.ResVo.class);
    }

    @ApiOperation("修改")
    @PostMapping("datasourceManage/update")
    public void sync(@RequestBody SysDiffDatasourceUpOrAddVo.UpVo upVo) {
        sysDiffDatasourceService.updateById(upVo);
    }

    @ApiOperation("删除")
    @PostMapping("datasourceManage/delete")
    public void delete(@RequestBody Long sysDiffDatasourceId) {
        sysDiffDatasourceService.removeById(sysDiffDatasourceId);
    }

    @ApiOperation("同步")
    @PostMapping("datasourceManage/sync")
    public void sync(@RequestBody Long datasourceId) {
        sysDiffDatasourceService.sync(datasourceId);
    }

    @ApiOperation("测试连接")
    @PostMapping("datasourceManage/testConnection")
    public void testConnection(@RequestBody DatasourceConnectionTestReq req) {
        datasourceService.testConnection(req);
    }


    @ApiOperation("数据库比较")
    @PostMapping("diff/analysis")
    public ResultData add(@RequestBody DbDiffReq req) {
        return dbDifService.dif(req);
    }

    @ApiOperation("数据库排序")
    @PostMapping("position/sort")
    public String sort(@RequestBody DiffInfo diffInfo) {
        // 通过数据库名获取数据库链接
        SysDiffDatasource leftDatasource = datasourceService.getDatasourceByName(diffInfo.getLeftData());
        SysDiffDatasource rightDatasource = datasourceService.getDatasourceByName(diffInfo.getRightData());

        Connection leftConnection = getconnection(leftDatasource);
        Connection rightConnection = getconnection(rightDatasource);

        // 通过数据库链接,获取字段的顺序信息
        Map<String, List<FieldInfo>> leftTableInfos = getTableInfos(leftConnection, leftDatasource.getDatabaseName());
        Map<String, List<FieldInfo>> rightTableInfos = getTableInfos(rightConnection, rightDatasource.getDatabaseName());

        // 通过字段的字段信息,进行比对
        String diffInfoRes = diff(leftTableInfos, rightTableInfos);
        return diffInfoRes;
    }

    /**
     * 获取表的移动信息
     *
     * @param connection
     * @return
     */
    private Map<String, List<FieldInfo>> getTableInfos(Connection connection, String databaseName) {

        String sql = "SELECT\n" +
                "\tCONCAT('ALTER TABLE ',TABLE_NAME,' MODIFY COLUMN ',COLUMN_NAME,' ',COLUMN_TYPE,\n" +
                "\tCASE\n" +
                "\t\t\tWHEN EXTRA LIKE '%auto_increment%' THEN\n" +
                "\t\t\t' AUTO_INCREMENT' ELSE '' \n" +
                "\tEND,\n" +
                "\tCASE\n" +
                "\t\t\tWHEN IS_NULLABLE = 'NO' THEN\n" +
                "\t\t\t' NOT NULL' ELSE ' NULL' \n" +
                "\tEND,\n" +
                "\tCASE\n" +
                "\t\t\tWHEN COLUMN_DEFAULT IS NOT NULL THEN\n" +
                "\t\t\tCONCAT( ' DEFAULT ', CASE WHEN COLUMN_TYPE LIKE '%char%' OR COLUMN_TYPE LIKE '%text%' THEN CONCAT( '\\'', COLUMN_DEFAULT, '\\'' ) ELSE COLUMN_DEFAULT \t\t\tEND ) ELSE '' \n" +
                "\tEND,\n" +
                "\t\t' COMMENT',' \\'',COLUMN_COMMENT,'\\'' \n" +
                "\t) AS alter_statement,\n" +
                "\tTABLE_NAME AS tabel_name,\n" +
                "\tCOLUMN_NAME AS \"column_name\",\n" +
                "\tCOLUMN_TYPE AS \"column_type\",\n" +
                "\tORDINAL_POSITION AS \"position\" \n" +
                "FROM\n" +
                "\tINFORMATION_SCHEMA.COLUMNS \n" +
                "WHERE\n" +
                "\tTABLE_SCHEMA = '" + databaseName + "';";


        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            Map<String, List<FieldInfo>> databaseInfo = new HashMap<>();
            while (resultSet.next()) {
                String tabelName = resultSet.getString("tabel_name");
                String columnName = resultSet.getString("column_name");
                String columnType = resultSet.getString("column_type");
                String alterStatement = resultSet.getString("alter_statement");

                int position = resultSet.getInt("position");
                if (!databaseInfo.containsKey(tabelName)) databaseInfo.put(tabelName, new ArrayList<>());
                databaseInfo.get(tabelName).add(position - 1, new FieldInfo(columnName, columnType, alterStatement));
            }
            return databaseInfo;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }


    /**
     * 获取数据库链接对象
     *
     * @param datasource
     * @return
     */
    private Connection getconnection(SysDiffDatasource datasource) {
        if (datasource == null) throw new RuntimeException("数据源不存在");
        DatasourceSynchronizer synchronizer = null;

        DatasourceType datasourceType = DatasourceType.of(datasource.getType());
        switch (datasourceType) {
            case MYSQL:
                synchronizer = new MysqlDatasourceSynchronizer(datasource.getUrl(), datasource.getUserName(), datasource.getPassword());
                break;
            case H2:
                synchronizer = new H2DatasourceSynchronizer(datasource.getUrl(), datasource.getUserName(), datasource.getPassword());
                break;
            case PGSQL:
                synchronizer = new PgsqlDatasourceSynchronizer(datasource.getUrl(), datasource.getUserName(), datasource.getPassword());
                break;
            case ORACLE:
                synchronizer = new OracleDatasourceSynchronizer(datasource.getUrl(), datasource.getUserName(), datasource.getPassword());
                break;
            case TIDB:
            default:
                throw new IllegalArgumentException("暂不支持: " + datasourceType);
        }
        try {
            return synchronizer.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException("连接数据库失败");
        }


    }

    /**
     * 比对数据
     *
     * @return
     */
    private String diff(Map<String, List<FieldInfo>> left, Map<String, List<FieldInfo>> right) {
        StringBuffer restStr = new StringBuffer();

        for (String key : left.keySet()) {
            if (right.get(key) != null) {
                List<FieldInfo> leftValue = left.get(key);
                List<FieldInfo> rightValue = right.get(key);

                // 判断一下他们是否全部一样
                Boolean sameFlg = true;
                for (int i = 0; i < leftValue.size(); i++) {
                    FieldInfo leftInfo = leftValue.get(i);
                    FieldInfo rightInfo = rightValue.get(i);
                    if (!leftInfo.getName().equals(rightInfo.getName())) {
                        sameFlg = false;
                        break;
                    }
                }

                // 如果表中的顺序不一致的时候,进行处理
                if (!sameFlg) {
                    // 处理数据,生成sql
                    Boolean firstFlg = true;
                    final String tableName = key;
                    String befrightName = null;


                    for (FieldInfo leftInfo : leftValue) {
                        for (int i = 0; i < rightValue.size(); i++) {
                            FieldInfo rightInfo = rightValue.get(i);
                            if (leftInfo.getName().equals(rightInfo.getName())) {
                                if (firstFlg) {
                                    restStr.append("<br><br>");
                                    restStr.append("##########################   ");
                                    restStr.append(key);
                                    restStr.append("   ##########################<br>");

                                    //  alter table 表名 modify 字段名 字段类型 after 字段 first
                                    String sql = rightInfo.getAlterStatement() + " first;";
                                    firstFlg = false;
                                    befrightName = rightInfo.getName();
                                    restStr.append(sql + "<br>");
                                } else {
                                    // alter table 表名 modify 字段名 字段类型 after 字段
                                    String sql = rightInfo.getAlterStatement() + " after `" + befrightName + "`;";
                                    befrightName = rightInfo.getName();
                                    restStr.append(sql + "<br>");
                                }
                            }
                        }
                    }
                }
            }
        }

        return restStr.toString();
    }


    @Data
    @AllArgsConstructor
    public static class FieldInfo {
        private String name;
        private String type;
        private String alterStatement;
    }

    @Data
    @ApiModel("排序信息")
    public static class DiffInfo {
        private String leftData;
        private String rightData;

    }


}
